﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using TestForm.entity;

namespace TestForm
{
    public partial class DbParam : Form
    {
        public static string dbPath = "api.db";

        public DbParam()
        {
            InitializeComponent();
        }

        public DbParam(String jybh)
        {
            InitializeComponent();
            textBox1.Text = jybh;
        }

        public static int BulkInsertData(List<InterfaceParam> data)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";Version=3;"))
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "INSERT INTO interface_param (infno, io_type, node_id, node_list, sequence_number, param_code, param_name, param_type, param_length, code_identifier, is_required, description) VALUES(@infno, @io_type, @node_id, @node_list, @sequence_number, @param_code, @param_name, @param_type, @param_length,@code_identifier, @is_required,@description)";
                        SQLiteParameter param1 = command.Parameters.Add("@infno", DbType.String);
                        SQLiteParameter param2 = command.Parameters.Add("@io_type", DbType.String);

                        SQLiteParameter param3 = command.Parameters.Add("@node_id", DbType.String);
                        SQLiteParameter param4 = command.Parameters.Add("@node_list", DbType.String);
                        SQLiteParameter param5 = command.Parameters.Add("@sequence_number", DbType.String);
                        SQLiteParameter param6 = command.Parameters.Add("@param_code", DbType.String);
                        SQLiteParameter param7 = command.Parameters.Add("@param_name", DbType.String);
                        SQLiteParameter param8 = command.Parameters.Add("@param_type", DbType.String);
                        SQLiteParameter param9 = command.Parameters.Add("@param_length", DbType.String);
                        SQLiteParameter param10 = command.Parameters.Add("@code_identifier", DbType.String);
                        SQLiteParameter param11 = command.Parameters.Add("@is_required", DbType.String);
                        SQLiteParameter param12 = command.Parameters.Add("@description", DbType.String);

                        // 遍历要插入的数据
                        foreach (InterfaceParam item in data)
                        {
                            param1.Value = item.infno;
                            param2.Value = item.io_type;

                            param3.Value = item.node_id;
                            param4.Value = item.node_list;
                            param5.Value = item.sequence_number;
                            param6.Value = item.param_code;
                            param7.Value = item.param_name;
                            param8.Value = item.param_type;
                            param9.Value = item.param_length;
                            param10.Value = item.code_identifier;
                            param11.Value = item.is_required;
                            param12.Value = item.description;
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit(); 
                    }
                }
            }
            return data.Count;
        }

        /// <summary>
        /// 保存参数信息
        /// </summary>
        /// <param name="infno"></param>
        /// <param name="io_type"></param>
        /// <param name="node_id"></param>
        /// <param name="node_list"></param>
        /// <param name="sequence_number"></param>
        /// <param name="param_code"></param>
        /// <param name="param_name"></param>
        /// <param name="param_type"></param>
        /// <param name="param_length"></param>
        /// <param name="code_identifier"></param>
        /// <param name="is_required"></param>
        /// <param name="description"></param>
        /// <returns></returns>
        private static int deleteInterfaceParam(String jybh,String param_code)
        {
            int count = 0;
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";Version=3;"))
            {
                connection.Open();
                string ideleteSql = "delete  from  interface_param where infno = @infno and  param_code = @param_code";
                SQLiteCommand deleteCmd = new SQLiteCommand(ideleteSql, connection);
                deleteCmd.Parameters.AddWithValue("@infno", jybh);
                deleteCmd.Parameters.AddWithValue("@param_code", param_code);
                count = deleteCmd.ExecuteNonQuery();
                connection.Close();
            }
            return count;
        }

        private static int deleteInterfaceParam1(String jybh)
        {
            int count = 0;
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";Version=3;"))
            {
                connection.Open();
                string ideleteSql = "delete  from  interface_param where infno = @infno";
                SQLiteCommand deleteCmd = new SQLiteCommand(ideleteSql, connection);
                deleteCmd.Parameters.AddWithValue("@infno", jybh);
                count = deleteCmd.ExecuteNonQuery();
                connection.Close();
            }
            return count;
        }

        /// <summary>
        ///  更新交易参数
        /// </summary>
        /// <param name="jybh"></param>
        /// <param name="input"></param>
        /// <param name="output"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        private static int updateInterface(List<InterfaceParam> data)
        {
            int delete_flag = deleteInterfaceParam1(data.First().infno.ToString());
            int insert_flag = BulkInsertData(data);
            return delete_flag + insert_flag;
        }

        /// <summary>
        ///  查询交易参数
        /// </summary>
        /// <param name="jybh"></param>
        /// <returns></returns>
        private static List<InterfaceParam> selectInterfaceParam(string infno)
        {
            List<InterfaceParam> item = new List<InterfaceParam>();
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";Version=3;"))
            {
                connection.Open();
                string selectSql = "select * from interface_param where infno = @infno";
                
                SQLiteCommand selectCmd = new SQLiteCommand(selectSql, connection);
                selectCmd.Parameters.AddWithValue("@infno", infno);
                using (SQLiteDataReader reader = selectCmd.ExecuteReader())
                {
                    InterfaceParam interfaceInfo = null;
                    while (reader.Read())
                    {
                        interfaceInfo = new InterfaceParam();
                        interfaceInfo.id = reader.GetInt32(0);
                        interfaceInfo.infno = reader.GetInt32(1);
                        interfaceInfo.io_type = reader.GetString(2);
                        interfaceInfo.node_id = reader.GetString(3);
                        interfaceInfo.node_list = reader.GetString(4);
                        interfaceInfo.sequence_number = reader.GetInt32(5);
                        interfaceInfo.param_code = reader.GetString(6);
                        interfaceInfo.param_name = reader.GetString(7);
                        interfaceInfo.param_type = reader.GetString(8);
                        interfaceInfo.param_length = reader.GetInt32(9);
                        interfaceInfo.code_identifier = reader.GetString(10);
                        interfaceInfo.is_required = reader.GetString(11);
                        interfaceInfo.description = reader.GetString(12);
                        item.Add(interfaceInfo);
                    }
                    connection.Close();
                }
            }
            return item;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String jybh = textBox1.Text;
            String io_type = comboBox1.Text;
            String node_id = textBox2.Text;
            String node_list = comboBox2.Text;
            if (jybh=="" || io_type=="" || node_id=="" || node_list =="") {
                MessageBox.Show("请填写交易编号，入出类型，节点标识,列表节点！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            List<InterfaceParam> items = new List<InterfaceParam>();
            InterfaceParam item = null;
            // 遍历每一行
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                String sequence_number = Convert.ToString(row.Cells["Column1"].Value);
                String param_code = Convert.ToString(row.Cells["Column2"].Value);
                if ((sequence_number != "") && (param_code != ""))
                {
                    item = new InterfaceParam();
                    item.param_code = param_code;
                    item.sequence_number = Convert.ToInt32(sequence_number);
                    item.param_name = Convert.ToString(row.Cells["Column3"].Value);
                    item.param_type = Convert.ToString(row.Cells["Column4"].Value);
                    item.param_length = Convert.ToInt32(row.Cells["Column5"].Value);
                    item.code_identifier = Convert.ToString(row.Cells["Column6"].Value);
                    item.is_required = Convert.ToString(row.Cells["Column7"].Value);
                    item.description = Convert.ToString(row.Cells["Column8"].Value);

                    item.infno = Convert.ToInt32(jybh);
                    item.io_type = io_type;
                    item.node_id = node_id;
                    item.node_list = node_list;
                    items.Add(item);
                }
            }

            if (items.Count > 0)
            {
                int delete_flag = deleteInterfaceParam1(items.First().infno.ToString());
                int counts = BulkInsertData(items);
                if (counts == dataGridView1.Rows.Count - 1)
                {
                    MessageBox.Show("保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("保存失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void DbParam_Shown(object sender, EventArgs e)
        {
            load_data();
        }

        private void load_data() {
            String jybh = textBox1.Text;
            this.dataGridView1.DataSource = null;
            dataGridView1.AutoGenerateColumns = false;
            List<InterfaceParam> items = selectInterfaceParam(jybh);
            if (items.Count<=0) {
                return;
            }

            DataTable dataTable = new DataTable("MyDataTable");

            textBox1.Text = items.First().infno.ToString();
            comboBox1.Text = items.First().io_type.ToString();
            textBox2.Text = items.First().node_id.ToString();
            comboBox2.Text = items.First().node_list.ToString();

            // 添加列到 DataTable
            dataTable.Columns.Add("Column1", typeof(string));
            dataTable.Columns.Add("Column2", typeof(string));
            dataTable.Columns.Add("Column3", typeof(string));
            dataTable.Columns.Add("Column4", typeof(string));
            dataTable.Columns.Add("Column5", typeof(string));
            dataTable.Columns.Add("Column6", typeof(string));
            dataTable.Columns.Add("Column7", typeof(string));
            dataTable.Columns.Add("Column8", typeof(string));

            foreach (var hhh in items) {
                dataTable.Rows.Add(hhh.sequence_number, hhh.param_code,hhh.param_name,hhh.param_type,hhh.param_length,hhh.code_identifier,hhh.is_required,hhh.description);
            }
            dataGridView1.DataSource =dataTable;
            dataGridView1.ClearSelection();
        }

    }
}
